using System.Text;
using BaseData.CommonLib.Models;
using Dapper;
using Microsoft.Data.SqlClient;
using Swimj.CommonLib.Base;
using Swimj.DataCore;

namespace BaseData.ApplicationLib.Queries
{
    public class CustomerQueries : ICustomerQueries
    {
        private string? _defaultConnStr;

        public CustomerQueries(string? defaultConnStr)
        {
            this._defaultConnStr = defaultConnStr;
        }

        public async Task<Result<CustomerInfoResponseDto>?> GetCustomer(int id)
        {
            CustomerInfoResponseDto? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                StringBuilder sqlBuilder = new StringBuilder(@"select id ,
       code,
       name,
       phone,
       email,
       address,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_CUSTOMER where 1 = 1  and id = @Id");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("Id", id);
                data = await db.QueryFirstOrDefaultAsync<CustomerInfoResponseDto>(sqlBuilder.ToString(), parameters);
            }

            return await Result<CustomerInfoResponseDto>.SuccessAsync(data);
        }

        public async Task<Result<IList<CustomerInfoResponseDto>?>> QueryCustomers(CustomerSearchRequestDto model)
        {
            IEnumerable<CustomerInfoResponseDto>? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                StringBuilder sqlBuilder = new StringBuilder(@"
select id ,
       code,
       name,
       phone,
       email,
       address,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_CUSTOMER where 1 = 1 ");

                DynamicParameters parameters = new DynamicParameters();

                data = await db.QueryAsync<CustomerInfoResponseDto>(sqlBuilder.ToString(), parameters);
            }

            return await Result<IList<CustomerInfoResponseDto>>.SuccessAsync(data.ToList());
        }

        public async Task<PaginatedResult<CustomerInfoResponseDto>> QueryCustomersByPage(CustomerSearchRequestDto model)
        {
            IEnumerable<CustomerInfoResponseDto>? data;
            int totalCount;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);

                StringBuilder sqlBuilder = new StringBuilder(@"select id ,
       code,
       name,
       phone,
       email,
       address,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_CUSTOMER where 1 = 1 ");

                DynamicParameters parameters = new();

                if (!string.IsNullOrEmpty(model.Code))
                {
                    sqlBuilder.Append(" and code like @Code ");
                    parameters.Add("Code", $"%{model.Code}%");
                }

                if (!string.IsNullOrEmpty(model.Name))
                {
                    sqlBuilder.Append(" and name like @Name");
                    parameters.Add("Name", $"%{model.Name}%");
                }

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

                data = await db.QueryAsync<CustomerInfoResponseDto>(query.SqlPage, parameters);

                // var totalSqlBuilder = new StringBuilder(@"select count(1) from MES_CUSTOMER t where 1 = 1 ");
                //
                // DynamicParameters totalParameters = new();
                //
                // if (!string.IsNullOrEmpty(model.Code))
                // {
                //     totalSqlBuilder.Append(" and code like @Code ");
                //     totalParameters.Add("Code", $"%{model.Code}%");
                // }
                //
                // if (!string.IsNullOrEmpty(model.Name))
                // {
                //     totalSqlBuilder.Append(" and name like @Name");
                //     totalParameters.Add("Name", $"%{model.Name}%");
                // }

                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount, parameters);
            }

            return await PaginatedResult<CustomerInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
                model.Page,
                model.Limit);
        }
    }
}